package LocalCRM::Doc::Detail;

use base 'LocalCRM::Doc';
use Data::Dumper;
use IO::File;
use POSIX;

sub new
{
    my $self = shift->SUPER::new();
    my %arg = @_;

    if ( $arg{id} )
    {
	if ( $self->{invoice} = $self->{_db}->getById($arg{id}) )
	{
	    $self->{_id} = $arg{id};

	    #if ( $self->{invoice}->{executive_id} != 3 ) { return undef; }

	    #if ( $self->{invoice}->{executive_id} == 3 )
	    #{
		my $dbi = $self->{_db}->{_db};

		# Выбираем услуги телефонии
		my $sth = $dbi->prepare
		(
		    'SELECT `id`'
		    .' FROM `service`'
		    .' WHERE `account_id`=?'
			.' AND `executive_id`=?'
			.' AND (`service_name_id`=3 OR `service_name_id`=30)',
		);

		if ( $sth->execute($self->{invoice}->{account_id},$self->{invoice}->{executive_id}) )
		{
			open(FILE,">>/tmp/log");
			# Делаем выборку звонков для каждой услуги
		    while ( my $row = $sth->fetchrow_hashref() )
		    {
				#$query = sprintf
				 #   'SELECT '
				#	.'spn.phone_number,'
				#	.'det.dst,'
				#	.'det.duration,'
				#	.'CONCAT(SUBSTRING_INDEX(det.cost,\'.\',1),\',\',SUBSTRING_INDEX(det.cost,\'.\',-1)) as cost,'
				#	.'det.date,'
				#	.'det.cost as cost_float,'
				#	.'pd.title'
				 #   .' FROM detalisation det'
				  #  .' JOIN service_phone_number spn ON det.service_phone_number_id = spn.id'
				   # .' JOIN service ser ON ser.id = spn.service_id'
				    #.' JOIN phone_direction pd ON det.phone_direction_id = pd.id'
				    #.' WHERE ser.id=%1$d'
					#.' AND date BETWEEN DATE_FORMAT(DATE(\'%2$s\'),\'%%Y-%%m-01\') AND DATE_SUB(DATE_ADD(DATE(\'%2$s\'), INTERVAL 1 MONTH), INTERVAL 1 SECOND)'
				    #.' ORDER BY `det`.`date`',

				$query="SELECT
							spn.phone_number,
							det.dst,
							det.duration,
							CONCAT(SUBSTRING_INDEX(det.cost,
							'.',
							1),
							',',
							SUBSTRING_INDEX(det.cost,
							'.',
							-1)) as cost,
							det.date,
							det.cost as cost_float,
							pd.title
						FROM
							detalisation det JOIN service_phone_number spn
								ON
								det.service_phone_number_id = spn.id JOIN service ser
								ON
								ser.id = spn.service_id JOIN phone_direction pd
								ON
								det.phone_direction_id = pd.id
						WHERE
							det.duration>5 AND
							ser.id=$row->{id} AND
							date BETWEEN CONCAT(YEAR('$self->{invoice}->{date}'),\"-\",MONTH('$self->{invoice}->{date}'),\"-01\") AND
							DATE_SUB(DATE_ADD('$self->{invoice}->{date}',INTERVAL 1 DAY),INTERVAL 1 SECOND)
						ORDER BY
							`det`.`date`;";
				
				print FILE $query."\n";
				
			    #$row->{id},
			    #$self->{invoice}->{date}->string();

				my $sth2 = $dbi->prepare($query);
				if ( $sth2->execute() )
				{
				    my $total;
				    push @{$self->{data}}, $self->utf2win('От;Куда;Продолжительность (мин.);Стоимость;Дата;Направление');
				    while ( my @row = $sth2->fetchrow_array() )
				    {
						#$row[2] = ($row[2]%60>5) ? ceil($row[2]/60) : floor($row[2]/60);
						$row[2] = ceil($row[2]/60);

						$total->{number} = $row[0] if ( !$total->{number} );
						$total->{duration} += $row[2];
						$total->{summ} += $row[5];
						$total->{count}++;

						push @{$self->{data}}, $self->utf2win(join(';',($row[0],$row[1],$row[2],$row[3],$row[4],$row[6])));
				    }
				    if ( $total->{count}>0 )
				    {
						push @{$self->{data}}, ';';
						push @{$self->{data}}, $self->utf2win(sprintf 'ИТОГО ПО НОМЕРУ %s: ЗВОНКОВ: %d ПРОДОЛЖИТЕЛЬНОСТЬ: %d мин. НА СУММУ: %0.2f',$total->{number},$total->{count},$total->{duration},$total->{summ});
						push @{$self->{data}}, '########################################################################;';
				    }
				}
		    }
		    close(FILE);
		}
	    #}
	}
    }

    return $self;
}

sub save
{
    my $self = shift;

    if ( $self->{_id} )
    {
	if ( my $filename = $self->filename('csv') )
	{
	    unless ( -d(sprintf '%s/%04d',$self->{_cfg}->val('Path','files'), $self->{invoice}->{account_id}) ) { mkdir(sprintf '%s/%04d',$self->{_cfg}->val('Path','files'), $self->{invoice}->{account_id}); }
	    unless ( -d(sprintf '%s/%04d/invoice',$self->{_cfg}->val('Path','files'), $self->{invoice}->{account_id}) ) { mkdir(sprintf '%s/%04d/invoice',$self->{_cfg}->val('Path','files'), $self->{invoice}->{account_id}); }

	    my $fh = IO::File->new( sprintf '>%s/%04d/invoice/%s', $self->{_cfg}->val('Path','files'), $self->{invoice}->{account_id}, $filename )||die("fuck");
	    print $fh join("\r\n",@{$self->{data}});
	    $fh->close();

	    return $filename;
	}
    }
}

1;
